<?php
/**
 * Page to Database class
 *
 * $Rev::            	$:  Revision of last commit
 * $Author::@author    	$:  Author of last commit
 * $Date:: 				$:  Date of last commit
 *
 * @package eesc
 */ 

/**
 * Require rules
 */
require_once BASE_URI . '/inc/config.inc.php';
require_once BASE_URI . './lib/TIS.class.php';
require_once BASE_URI . './inc/functions.inc.php';

/**
 * Database Class
 * 
 * Offering similar functionality as the TIS class
 * Interface layer with the student choice specific
 * database. 
 * 
 * @package eesc
 * @subpackage classes
 */
class Database {
	
	protected $db;
	
	/**
	 * Connect to the DB
	 * 
	 * @deprecated deprecated since version 0.1
	 */
	public function connect()
	{	
		/*include BASE_URI . "./inc/mysql.conn.inc.php";
		
		$this->db = mysql_connect($mysql_host, $mysql_user, $mysql_pass) or trigger_error('Could not connect: ' . mysql_error());
		
		mysql_select_db($mysql_name) or trigger_error('Could not select database');*/
		
		/*$this->db = new mysqli($mysql_host,$mysql_user,$mysql_pass,$mysql_name);
		
		if (mysqli_connect_errno()) {
		    trigger_error("Connect failed:" . mysqli_connect_errno() ." " . mysqli_connect_error());
		    exit();
		}*/
	}
	
	/**
	 * Database constructor
	 * 
	 * Attempts connection to the database and returns true if successful
	 * Failure to connect returns false and triggers an error,
	 * 
	 * @return bool
	 */
	public function __construct() {
		
		include BASE_URI . "./inc/mysql.conn.inc.php";
		if ($this->db = mysql_connect($mysql_host, $mysql_user, $mysql_pass)) {
			if (mysql_select_db($mysql_name)) {
				return true;
			} else {
				trigger_error('Could not select database');
				return false;
			}
		} else {
			trigger_error('Could not connect: ' . mysql_error());
			return false;
		}
	}
	
	/**
	 * 
	 * @param string $table					tablename to select from
	 * @param string $columns [optional]	colums to select
	 * @param string $extra [optional]		anything else to add to the select query
	 * @return array $data					the rows from the select
	 */
	public function select($table, $columns = "*", $extra = NULL) {
		
		if (is_string($columns)) {
			$columns = explode(",", $columns);
		}

		$query = "SELECT " . implode(", ", $columns);
		$query .= " FROM ". TABLE_PREFIX . $table;
		if (isset($extra)) {
			$query .= " ".$extra;
		}
		$query .= ";";
		
		$data = array();
		
		if(!$result = mysql_query($query)) {
			trigger_error("MYSQL Error" . mysql_errno() . " " . mysql_error());
			return false;
		} else {
			while ($row = mysql_fetch_assoc($result)) {
				$data[] = $row;
			}
			return $data;
		}
		
		/*if(!$result = $this->db->query($query)) {
			trigger_error("MYSQL Error" . $db->errorno . " " . $db->error);
			return false;
		} else {
			$data = $result->fetch_all(MYSQLI_ASSOC);
			return $data;
		}*/
	}
	
	/**
	 * Insert a row into the database
	 * 
	 * @param string $table					tablename to select from
	 * @param string $assoc_array			key value pairs of the data to insert
	 * @return bool
	 */
	public function insert($table, $assoc_array) {
		$query = "INSERT INTO " .  TABLE_PREFIX .$table . " (`". implode("`,`", array_keys($assoc_array)) ."`) VALUES ('" . implode("','", array_values($assoc_array)) . "');";
		
		if (!$result = mysql_query($query)) {
			trigger_error("MYSQL Error" . mysql_errno() . " " . mysql_error());
			return false;
		} else {
			return array("highlight" => mysql_affected_rows() . " record inserted");
			
		}
		
		/*if (!$result = $this->db->query($query)) {
			trigger_error("MYSQL Error" . $db->errorno . " " . $db->error);
			return false;
		} else {
			return array("highlight" => $this->db->affected_rows . " record inserted");
		}*/
		
	}
	
	/**
	 * Update a row in the database
	 * 
	 * @param string $table					tablename to select from
	 * @param object $where					key value pairs as conditionals
	 * @param string $assoc_array			key value pairs of the data to insert
	 * @return bool
	 */
	public function update($table, $where, $assoc_array) {
		
		$query = "UPDATE " .  TABLE_PREFIX .$table;
		
		array_walk($assoc_array, 'assoc2Conditionals');
		$query .= " SET ". implode(", ",$assoc_array);
		
		array_walk($where, 'assoc2Conditionals');
		$query .= " WHERE". implode(" AND ",$where);

		if (!$result = mysql_query($query)) {
			trigger_error("MYSQL Error" . mysql_errno() . " " . mysql_error());
			return false;
		} else {
			return array("highlight" => mysql_affected_rows() . " record inserted");
			
		}
		
		/*$query = "UPDATE " .  TABLE_PREFIX .$table;
		
		array_walk($assoc_array, 'assoc2Conditionals');
		$query .= " SET ". implode(", ",$assoc_array);
		
		array_walk($where, 'assoc2Conditionals');
		$query .= " WHERE". implode(" AND ",$where);

		if (!$result = $this->db->query($query)) {
			trigger_error("MYSQL Error" . $db->errorno . " " . $db->error);
			return false;
		} else {
			return array("highlight" => $this->db->affected_rows . " record updated");
		}*/

	}
	
	/**
	 * Delete a row from the database
	 * 
	 * @param String $table
	 * @param integer $id
	 * @return bool
	 */
	public function delete($table, $where) {
		
		$query = "DELETE FROM " .  TABLE_PREFIX .$table;
		
		array_walk($where, 'assoc2Conditionals');
		$query .= " WHERE ". implode(" AND ",$where);
		
		if (!$result = mysql_query($query)) {
			trigger_error("MYSQL Error" . mysql_errno() . " " . mysql_error());
			return false;
		} else {
			return array("highlight" => mysql_affected_rows() . " record inserted");
			
		}
		
		/*$query = "DELETE FROM " .  TABLE_PREFIX .$table;
		
		array_walk($where, 'assoc2Conditionals');
		$query .= " WHERE ". implode(" AND ",$where);
		
		if (!$result = $this->db->query($query)) {
			trigger_error("MYSQL Error" . $db->errorno . " " . $db->error);
			return false;
		} else {
			return array("highlight" => $this->db->affected_rows . " record deleted");
		}*/
	}
	
	/**
	 * Retrieve and the table from the database into a format
	 * 
	 * @param string $table
	 * @param string $format [optional]
	 * @param array $columns [optional]
	 * @param string $extra [optional]
	 * @return string $html
	 */
	public function getTable($table, $format = "html", $columns = "*", $extra = "") {
		$data = $this->select($table, $columns,$extra);
		if ($format == "html") {
			$html = "";
			
			$html .= "<table id =\"". $table ."table\">";
			
			$html .= "<thead><tr>";
			foreach (array_keys($data[0]) as $heading) {
					$html .= "<th>$heading</th>";
				
			}
			$html .= "<th>action</th>";
			$html .= "</tr></thead><tbody>";
			foreach ($data as $row) {
				$html .= "<tr>";
				foreach ($row as $val) {
					$html .= "<td>$val</td>";
				}
				$html .= "<td><a href=\"admin.php?manage=$table&amp;action=edit&amp;id={$row['id']}\">edit</a>";
				$html .= " <a href=\"admin.php?manage=$table&amp;action=delete&amp;id={$row['id']}\" onclick=\"return confirm('Are you sure?');\">delete</a></td>";
				$html .= "</tr>";
			}
			$html .= "</tbody></table>";
			
			return $html;
		}
	}
	
	/**
	 * Create the form dynamically from the database
	 * 
	 * @param string $table
	 * @param string $action
	 * @param integer $id [optional]					if we are updating
	 * @return string $html
	 */
	private function HTMLForm($table, $action, $id = "") {
		
		$query = "DESCRIBE " . TABLE_PREFIX . $table .";";

		
		if (!$result = mysql_query($query)) {
			trigger_error("MYSQL Error" . mysql_errno() . " " . mysql_error());
			return false;
		}
		
		$html = "<div id=\"$table\">";
		
		// Select all the existing data if we're updating the row
		if ($action == "update") {
			$values = $this->select($table, "*", "WHERE ID = ".$id);
			$html .= "<p>Editing $table:$id</p>";
		}
		
		$html .= "<table><form action=\"admin.php?manage=$table&amp;action=$action&amp;id=$id\" method=\"post\">";
		
		while($row = mysql_fetch_assoc($result)) {
			$field = $row["Field"];
			if ($field != "id") {
				$html .= "<tr><td><label for=\"$table-$field\">$field</label></td>";
				if ($field == "tripos") {
					$tis = new TIS();
					$triposes = $tis->getTriposes();
					$html .= "<td><select name=\"$field\">";
					foreach ($triposes as $tripos) {
						if ($action == "update") {
							if ($values[0][$field] == $tripos["A_INT_TriposGroupID"]) {
								$selected = "selected=\"selected\"";
							} else {
								$selected = "";
							}
						}
						$html .= "<option $selected name=\"tripos\" value=\"{$tripos["A_INT_TriposGroupID"]}\" id=\"tripos-{$tripos["A_INT_TriposGroupID"]}\" >{$tripos["STR_TriposGroupName"]}</option>";
					}
					$html .= "</select></td></tr>";
				} else {
					$html .= "<td><input size=\"48\" type=\"text\" id=\"$table-$field\" name= \"$field\"";
					if ($action == "update") {
						$html .= " value=\"{$values[0][$field]}\"";
					}
					$html .= "/></td></tr>";
				}
				
			}
		}
		$html .= "<input type=\"submit\" id=\"submit\" value=\"submit\" /></form></table>";
		$html .= "</div>";
		
		return $html;
		
		/*$query = "DESCRIBE " . TABLE_PREFIX . $table .";";

		
		if (!$result = $this->db->query($query)) {
			trigger_error("MYSQL Error" . $db->errorno . " " . $db->error);
			return false;
		}
		
		// Select all the existing data if we're updating the row
		if ($action == "update") {
			$values = $this->select($table, "*", "WHERE ID = ".$id);
		}
		
		$html = "<div id=\"$table\">";
		
		$html .= "<form action=\"admin.php?manage=$table&amp;action=$action&amp;id=$id\" method=\"post\">";
		
		while($row = $result->fetch_assoc()) {
			$field = $row["Field"];
			if ($field != "id") {
				$html .= "<label for=\"$table-$field\">$field</label>";
				$html .= "<input type=\"text\" id=\"$table-$field\" name= \"$field\"";
				if ($action = "update") {
					$html .= " value=\"{$values[0][$field]}\"";
				}
				$html .= "/><br />";
			}
		}
		$html .= "<input type=\"submit\" id=\"submit\" value=\"submit\" />";
		$html .= "</form>";
		$html .= "</div>";
		
		return $html;*/
		
	}
	
	/**
	 * Pass though to the htmlform
	 * 
	 * @param string $table
	 * @param string $id
	 * @return string $html
	 */
	public function edit($table, $id) {
		return $this->HTMLForm($table, "update", $id);
	}

	/**
	 * Pass though to the htmlform
	 * 
	 * @param string $table
	 * @return string $html
	 */
	public function add($table) {
		return $this->HTMLForm($table, "insert");
	}
	
	/**
	 * @todo figure out what this does
	 * @param string $table
	 * @param string $column
	 * @param string $extra [optional]
	 * @return string $html
	 */
	/*public function choose($table, $column, $extra = NULL) {
		$tis = new TIS();
		$courses = $tis->select($table,$column);
		
		foreach ($courses as $course) {
			$html .= "<input type=\"checkbox\" name=\"{$column}[]\" value=\"{$column[0]}\" /><label for=\"\"></label>";
		}
		return $html;
	}*/
}
?>
